package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;

import modelo.Cereal;
import modelo.Cosecha;
import modelo.Lote;
import modelo.Model;
import modelo.Periodo;
import modelo.Siembra;
import modelo.Temporada;
import modelo.TemporadaItem;

public class AdmPersistenciaTemporada {

	private static AdmPersistenciaTemporada instancia;

	public static AdmPersistenciaTemporada getinstancia (){
		if(instancia==null)
			instancia = new AdmPersistenciaTemporada();
		return instancia;
	}
	
	public Vector<Temporada> selectAll(){
		try{
			Vector<Temporada> resultados = new Vector<Temporada>();
			Connection connection = PoolConnection.getPoolConnecion().getConnection();
			Statement s = connection.createStatement();
			ResultSet rs = s.executeQuery("SELECT * FROM Temporada");
			while(rs.next()){
					int codigo = rs.getInt(1);
					Siembra si = Model.getInstance().buscarSiembra(rs.getInt(2));
					Cosecha c = Model.getInstance().buscarCosecha(rs.getInt(3));
					Cereal ce = Model.getInstance().buscarCereal(rs.getInt(4));
					Periodo p = Model.getInstance().buscarPeriodo(rs.getInt(5));
					
					Vector<TemporadaItem> vti = new Vector<TemporadaItem>();
					PreparedStatement ps2 = connection.prepareStatement("SELECT * FROM Temporada_item where codigo_temporada = ?");//revisar campos
					ps2.setInt(1, codigo);
					ResultSet rs2 = ps2.executeQuery();
					
					while(rs2.next()){
						Lote l = Model.getInstance().getLote(rs2.getInt(2));
						float cantC = rs2.getFloat(3);
						float cantS = rs2.getFloat(4);
						TemporadaItem ti = new TemporadaItem(l,cantC,cantS);
						vti.add(ti);
					}
					
					resultados.add(new Temporada(si,c,ce,p,vti));
					System.out.println("Se cargo Temporada : "+codigo);
				PoolConnection.getPoolConnecion().releaseConnection(connection);
				return resultados;
			}
		}
		catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	public Temporada select(int codigo){
		try{
		Temporada c = null;
		Connection connection = PoolConnection.getPoolConnecion().getConnection();
		PreparedStatement s= connection.prepareStatement("select * from Temporada where codigo_temporada = ?");
		s.setInt(1, codigo);
		ResultSet rs = s.executeQuery();
		
		Siembra si = Model.getInstance().buscarSiembra(rs.getInt(2));
		Cosecha co = Model.getInstance().buscarCosecha(rs.getInt(3));
		Cereal ce = Model.getInstance().buscarCereal(rs.getInt(4));
		Periodo p = Model.getInstance().buscarPeriodo(rs.getInt(5));
		
		Vector<TemporadaItem> vti = new Vector<TemporadaItem>();
		PreparedStatement ps2 = connection.prepareStatement("SELECT * FROM Temporada_item where codigo_temporada = ?");
		ps2.setInt(1, codigo);
		ResultSet rs2 = ps2.executeQuery();
		
		while(rs2.next()){
			Lote l = Model.getInstance().getLote(rs2.getInt(2));
			float cantC = rs2.getFloat(3);
			float cantS = rs2.getFloat(4);
			TemporadaItem ti = new TemporadaItem(l,cantC,cantS);
			vti.add(ti);
		}
		
		c = new Temporada(si,co,ce,p,vti);
		
		PoolConnection.getPoolConnecion().releaseConnection(connection);
		return c;
		}
		catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}
	
	public boolean existeTemporada(Periodo p) throws Exception{
		try{
		Temporada c = null;
		Connection connection = PoolConnection.getPoolConnecion().getConnection();
		PreparedStatement s= connection.prepareStatement("select * from Temporada where codigo_periodo = ?");
		s.setInt(1, p.getCodigo());
		ResultSet rs = s.executeQuery();
		PoolConnection.getPoolConnecion().releaseConnection(connection);
		if(rs.getRow()==0)
			return false;
		else
			return true;
		}
		catch (SQLException e) {
			e.printStackTrace();
			throw new Exception("No se pudo verificar existencia");
		}
	}
	
	
	public void insert(Temporada c){
		try{
			Connection connection = PoolConnection.getPoolConnecion().getConnection();
			PreparedStatement s= connection.prepareStatement("insert into Temporada (codigo_temporada,codigo_siembra,codigo_cosecha,codigo_cereal,codigo_periodo) values (?,?,?,?,?)");
			s.setInt(1, c.getCodigo());
			s.setInt(2, c.getSiembra().getCodigo());
			s.setInt(3,c.getCosecha().getId());
			s.setInt(4, c.getCereal().getCodigo());
			s.setInt(5, c.getPeriodo().getCodigo());
			s.executeQuery();
			PoolConnection.getPoolConnecion().releaseConnection(connection);
			}
			catch (SQLException e) {
				e.printStackTrace();
			}	
	}
	
	
	public void delete(Temporada c){
		try{
			Connection connection = PoolConnection.getPoolConnecion().getConnection();
			PreparedStatement s= connection.prepareStatement("delete * from Temporada where codigo = ?");
			s.setInt(1, c.getCodigo());
			s.executeQuery();
			PoolConnection.getPoolConnecion().releaseConnection(connection);
			}
			catch (SQLException e) {
				e.printStackTrace();
			}	
	}
	
}
